//轮播图
const router = require('koa-router')()
const fs = require("fs");
const path = require("path");
const uploadDir = path.join(__dirname, '../images/plantswiper');
router.prefix('/plantswiper')

function handleArr(arr) {
    for (let i = 0; i < arr.length; i++) {
        for (let y = i + 1; y < arr.length; y++) {
            if (arr[i] === arr[y]) {
                arr.splice(y, 1);
                y--;  //对应上文的splice删除元素的注意事项
            }
        }
    }
    return arr
}
//页面初始化时获取数据库内的数据条目
router.post('/getplantswiperbackend', async (ctx, next) => {
    //get cropsid
    let sqlcropsidall = 'SELECT cropsid FROM tags'
    let resultgetcropsidall = await ctx.db.EXCUTE(sqlcropsidall);
    handleArr(resultgetcropsidall);
    let middlearr = [];
    console.log(resultgetcropsidall); //[{cropsid:},{cropsid:}]
    for (let i = 0; i < resultgetcropsidall.length; i++) {
        middlearr.push(resultgetcropsidall[i].cropsid);
    }
    resultgetcropsidall = middlearr;
    console.log(resultgetcropsidall);//[,,,]

    let groupresultimg = [];

    for (let i = 0; i < resultgetcropsidall.length; i++) {
        let resultsame = await ctx.db.EXCUTE("SELECT (swiperimg) FROM plantswiper WHERE cropsid in (?)", [resultgetcropsidall[i]]);
        let resultsameobj = {};
        resultsameobj.swiperimg = [];
        for (let j = 0; j < resultsame.length; j++) {
            resultsameobj.swiperimg.push(resultsame[j].swiperimg);
        }
        console.log('obj:');
        console.log(resultsameobj);//{swiperimg:['','','']}
        groupresultimg.push(resultsameobj);
    }
    console.log(groupresultimg);//[[{swiperimg:},{}],[]]

    //获取所有的cropname
    let resultcropall = await ctx.db.EXCUTE("SELECT cropname AS cropall FROM tags")
    let result = await ctx.db.EXCUTE('SELECT * FROM plantswiper GROUP BY cropsid');
    for (let i = 0; i < result.length; i++) {
        Object.assign(result[i], resultcropall[i]);
        Object.assign(result[i], groupresultimg[i]);
    }
    ctx.body = {
        code: 200,
        data: result
    }
})

//编辑item 首先获取该项的内容
router.post('/getsingleplantswiperbackend', async (ctx, next) => {
    let id = ctx.request.body.id;

    let sql = 'SELECT * FROM plantswiper WHERE id=?';
    let result = await ctx.db.EXCUTE(sql, [id]);

    ctx.body = {
        code: 200,
        data: result
    }
})

//编辑 上传图片
router.post('/modifyplantswiperimgbackend', async (ctx, next) => {
    let id = ctx.request.body.id;
    let cropname = ctx.request.body.cropname;
    let swiperimg = ctx.request.body.swiperimg;

    //get cropsid
    let sqlcropsid = 'SELECT cropsid FROM tags WHERE cropname=?'
    let resultgetcropsid = await ctx.db.EXCUTE(sqlcropsid, [cropname]);
    console.log(resultgetcropsid[0].cropsid);

    let swiperimglist = await ctx.db.EXCUTE("select swiperimg from plantswiper where id=?", [id]);
    console.log(swiperimglist[0].swiperimg);
    if (swiperimglist[0].swiperimg == swiperimg) {
        for (let i = 0; i < swiperimg.length; i++) {
            let m = swiperimg[i].replace('/http://localhost:8080/', '')
            let n = swiperimg[i].split('http://localhost:8080/')[1]
            console.log(m);
            console.log(n);
            fs.unlink(path.join(__dirname, '../' + n), async function (err) {
                if (err) {
                    console.log(err);
                    ctx.body = {
                        code: 400
                    }
                }
                await ctx.db.EXCUTE("update plantswiper set swiperimg='' where id=?", [id]);
            })
        }
    }

    for (let i = 0; i < ctx.request.body.swiperimg.length; i++) {
        console.log('swiperimg[i]');
        console.log(swiperimg[i])
        if (!swiperimg[i].url.includes("http://localhost:8080")) {
            var base64Data = swiperimg[i].url.replace(/^data:image\/\w+;base64,/, "");
            var dataBuffer = Buffer.from(base64Data, 'base64');
            let imgUrl = uploadDir + '\\' + swiperimg[i].name
            fs.writeFileSync(imgUrl, dataBuffer)

            if (i == 0) {
                console.log(growid);
                console.log('/images/plantswiper/' + swiperimg[i].name);
            }
            let sql = 'UPDATE plantswiper SET cropsid=?, cropname=?, swiperimg=? WHERE id=?'
            await ctx.db.EXCUTE(sql, [resultgetcropsid[0].cropsid, cropname, '/images/plantswiper/' + swiperimg[i].name, id]);
        } else {
            if (i == 0) {
                console.log(swiperimg[0]);
                let n = swiperimg[i].url.split('http://localhost:8080')[1]
                console.log(n);
                let sql = 'UPDATE plantswiper SET cropsid=?, cropname=?, swiperimg=? WHERE id=?'
                await ctx.db.EXCUTE(sql, [resultgetcropsid[0].cropsid, cropname, '/images/plantswiper/' + swiperimg[i].name, id]);
            }
        }
    }

    //get cropsid
    let sqlcropsidall = 'SELECT cropsid FROM tags'
    let resultgetcropsidall = await ctx.db.EXCUTE(sqlcropsidall);
    handleArr(resultgetcropsidall);
    let middlearr = [];
    console.log(resultgetcropsidall); //[{cropsid:},{cropsid:}]
    for (let i = 0; i < resultgetcropsidall.length; i++) {
        middlearr.push(resultgetcropsidall[i].cropsid);
    }
    resultgetcropsidall = middlearr;
    console.log(resultgetcropsidall);//[,,,]

    let groupresultimg = [];

    for (let i = 0; i < resultgetcropsidall.length; i++) {
        let resultsame = await ctx.db.EXCUTE("SELECT (swiperimg) FROM plantswiper WHERE cropsid in (?)", [resultgetcropsidall[i]]);
        let resultsameobj = {};
        resultsameobj.swiperimg = [];
        for (let j = 0; j < resultsame.length; j++) {
            resultsameobj.swiperimg.push(resultsame[j].swiperimg);
        }
        console.log('obj:');
        console.log(resultsameobj);//{swiperimg:['','','']}
        groupresultimg.push(resultsameobj);
    }
    console.log(groupresultimg);//[[{swiperimg:},{}],[]]

    //获取所有的cropname
    let resultcropall = await ctx.db.EXCUTE("SELECT cropname AS cropall FROM tags")
    let result = await ctx.db.EXCUTE('SELECT * FROM plantswiper GROUP BY cropsid');
    for (let i = 0; i < result.length; i++) {
        Object.assign(result[i], resultcropall[i]);
        Object.assign(result[i], groupresultimg[i]);
    }
    ctx.body = {
        code: 200,
        data: result
    }

});

//删除项
router.post('/delswiperimgbackend', async (ctx, next) => {
    let cropsid = ctx.request.body.cropsid;
    let swiperimg = ctx.request.body.swiperimg;

    for (let i = 0; i < swiperimg.length; i++) {
        let m = swiperimg[i].replace('/http://localhost:8080/', '')
        let n = swiperimg[i].split('http://localhost:8080/')[1]
        fs.unlink(path.join(__dirname, '../' + n), async function (err) {
            if (err) {
                console.log(err);
                ctx.body = {
                    code: 400
                }
            }
            let sqldelbyid = 'DELETE FROM plantswiper WHERE cropsid=?'
            await ctx.db.EXCUTE(sqldelbyid, cropsid);
            ctx.body = {
                code: 200,
            }
        })
    }

    //get cropsid
    let sqlcropsidall = 'SELECT cropsid FROM tags'
    let resultgetcropsidall = await ctx.db.EXCUTE(sqlcropsidall);
    handleArr(resultgetcropsidall);
    let middlearr = [];
    console.log(resultgetcropsidall); //[{cropsid:},{cropsid:}]
    for (let i = 0; i < resultgetcropsidall.length; i++) {
        middlearr.push(resultgetcropsidall[i].cropsid);
    }
    resultgetcropsidall = middlearr;
    console.log(resultgetcropsidall);//[,,,]

    let groupresultimg = [];

    for (let i = 0; i < resultgetcropsidall.length; i++) {
        let resultsame = await ctx.db.EXCUTE("SELECT (swiperimg) FROM plantswiper WHERE cropsid in (?)", [resultgetcropsidall[i]]);
        let resultsameobj = {};
        resultsameobj.swiperimg = [];
        for (let j = 0; j < resultsame.length; j++) {
            resultsameobj.swiperimg.push(resultsame[j].swiperimg);
        }
        console.log('obj:');
        console.log(resultsameobj);//{swiperimg:['','','']}
        groupresultimg.push(resultsameobj);
    }
    console.log(groupresultimg);//[[{swiperimg:},{}],[]]

    //获取所有的cropname
    let resultcropall = await ctx.db.EXCUTE("SELECT cropname AS cropall FROM tags")
    let result = await ctx.db.EXCUTE('SELECT * FROM plantswiper GROUP BY cropsid');
    for (let i = 0; i < result.length; i++) {
        Object.assign(result[i], resultcropall[i]);
        Object.assign(result[i], groupresultimg[i]);
    }
    ctx.body = {
        code: 200,
        data: result
    }
});

//backend 增加项
router.post('/addplantswiperbackend', async (ctx, next) => {
    let cropname = ctx.request.body.cropname;
    let swiperimg = ctx.request.body.swiperimg;
    console.log(cropname)

    //cropsid
    let sqlcropsid = 'SELECT cropsid FROM tags WHERE cropname=?'
    let resultgetcropsid = await ctx.db.EXCUTE(sqlcropsid, [cropname]);
    console.log(resultgetcropsid[0].cropsid);

    var base64Data = swiperimg[0].url.replace(/^data:image\/\w+;base64,/, "");
    var dataBuffer = Buffer.from(base64Data, 'base64');
    let imgUrl = uploadDir + '\\' + swiperimg[0].name
    fs.writeFileSync(imgUrl, dataBuffer)

    let sqlstage1 = 'INSERT INTO plantswiper (cropsid,cropname,swiperimg) VALUES (?,?,?)'
    await ctx.db.EXCUTE(sqlstage1, [resultgetcropsid[0].cropsid, cropname, '/images/plantswiper/' + swiperimg[0].name]);

    //get cropsid
    let sqlcropsidall = 'SELECT cropsid FROM tags'
    let resultgetcropsidall = await ctx.db.EXCUTE(sqlcropsidall);
    handleArr(resultgetcropsidall);
    let middlearr = [];
    console.log(resultgetcropsidall); //[{cropsid:},{cropsid:}]
    for (let i = 0; i < resultgetcropsidall.length; i++) {
        middlearr.push(resultgetcropsidall[i].cropsid);
    }
    resultgetcropsidall = middlearr;
    console.log(resultgetcropsidall);//[,,,]

    let groupresultimg = [];

    for (let i = 0; i < resultgetcropsidall.length; i++) {
        let resultsame = await ctx.db.EXCUTE("SELECT (swiperimg) FROM plantswiper WHERE cropsid in (?)", [resultgetcropsidall[i]]);
        let resultsameobj = {};
        resultsameobj.swiperimg = [];
        for (let j = 0; j < resultsame.length; j++) {
            resultsameobj.swiperimg.push(resultsame[j].swiperimg);
        }
        console.log('obj:');
        console.log(resultsameobj);//{swiperimg:['','','']}
        groupresultimg.push(resultsameobj);
    }
    console.log(groupresultimg);//[[{swiperimg:},{}],[]]

    //获取所有的cropname
    let resultcropall = await ctx.db.EXCUTE("SELECT cropname AS cropall FROM tags")
    let result = await ctx.db.EXCUTE('SELECT * FROM plantswiper GROUP BY cropsid');
    for (let i = 0; i < result.length; i++) {
        Object.assign(result[i], resultcropall[i]);
        Object.assign(result[i], groupresultimg[i]);
    }
    ctx.body = {
        code: 200,
        data: result
    }

});


module.exports = router